﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using model;
using System.Data;
using System.Data.SqlClient;

namespace dao
{
    public class BaivietDao:BaseDao
    {
        public List<Baiviet> getAllBaiviet()
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "sp_baiviet_SelectAll";
            sqlCmd.Parameters.Clear();
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                List<Baiviet> glstBaiviet = new List<Baiviet>();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Baiviet bv = new Baiviet();
                        bv.ID = Convert.ToInt64(dr["ID"]);
                        bv.Mabaiviet = Convert.ToString(dr["Mabaiviet"]);
                        bv.Tenbaiviet = Convert.ToString(dr["Tenbaiviet"]);
                        bv.Shortcut = Convert.ToString(dr["shortcut"]);
                        bv.Noidung = Convert.ToString(dr["noidung"]);
                        bv.Anhdaidien = Convert.ToString(dr["anhdaidien"]);
                        bv.Tenloaibaiviet = Convert.ToString(dr["tenloaibaiviet"]);

                        glstBaiviet.Add(bv);
                    }
                    dr.Close();
                    sqlCmd.Connection.Close();
                }
                return glstBaiviet;
            }
        }

        public List<Baiviet> getBaivietTop1ByLoai(long idLoaibai)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "sp_baiviet_SelectTop1ByLoaibai";
            sqlCmd.Parameters.Clear();

            sqlCmd.Parameters.Add("@IDLoaibaiviet", SqlDbType.BigInt).Value = idLoaibai;
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                List<Baiviet> glstBaiviet = new List<Baiviet>();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Baiviet bv = new Baiviet();
                        bv.ID = Convert.ToInt64(dr["ID"]);
                        bv.Mabaiviet = Convert.ToString(dr["Mabaiviet"]);
                        bv.Tenbaiviet = Convert.ToString(dr["Tenbaiviet"]);
                        bv.Shortcut = Convert.ToString(dr["shortcut"]);
                        bv.Noidung = Convert.ToString(dr["noidung"]);
                        bv.Anhdaidien = Convert.ToString(dr["anhdaidien"]);

                        glstBaiviet.Add(bv);
                    }
                    dr.Close();
                    sqlCmd.Connection.Close();
                }
                return glstBaiviet;
            }
        }

        public List<Baiviet> getBaivietByLoai(long idLoaibai)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "sp_baiviet_SelectByLoaibai";
            sqlCmd.Parameters.Clear();

            sqlCmd.Parameters.Add("@IDLoaibaiviet", SqlDbType.BigInt).Value = idLoaibai;
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                List<Baiviet> glstBaiviet = new List<Baiviet>();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Baiviet bv = new Baiviet();
                        bv.ID = Convert.ToInt64(dr["ID"]);
                        bv.Mabaiviet = Convert.ToString(dr["Mabaiviet"]);
                        bv.Tenbaiviet = Convert.ToString(dr["Tenbaiviet"]);
                        bv.Shortcut = Convert.ToString(dr["shortcut"]);
                        bv.Noidung = Convert.ToString(dr["noidung"]);
                        bv.Anhdaidien = Convert.ToString(dr["anhdaidien"]);

                        glstBaiviet.Add(bv);
                    }
                    dr.Close();
                    sqlCmd.Connection.Close();
                }
                return glstBaiviet;
            }
        }

        public Baiviet getBaivietByID(long id)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "sp_baiviet_SelectRow";
            sqlCmd.Parameters.Clear();

            sqlCmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = id;
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                List<Baiviet> glstBaiviet = new List<Baiviet>();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Baiviet bv = new Baiviet();
                        bv.ID = Convert.ToInt64(dr["ID"]);
                        bv.Mabaiviet = Convert.ToString(dr["Mabaiviet"]);
                        bv.Tenbaiviet = Convert.ToString(dr["Tenbaiviet"]);
                        bv.Shortcut = Convert.ToString(dr["shortcut"]);
                        bv.Noidung = Convert.ToString(dr["noidung"]);
                        bv.Anhdaidien = Convert.ToString(dr["anhdaidien"]);
                        bv.IDLoaibaiviet = Convert.ToInt64(dr["IDLoaibaiviet"]);
                        glstBaiviet.Add(bv);
                    }
                    dr.Close();
                    sqlCmd.Connection.Close();
                }
                return glstBaiviet[0];
            }
        }

        public List<Baiviet> getTop3BaivietByLoai(long idLoaibai)
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "sp_baiviet_SelectTop3ByLoaibai";
            sqlCmd.Parameters.Clear();

            sqlCmd.Parameters.Add("@IDLoaibaiviet", SqlDbType.BigInt).Value = idLoaibai;
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Open();

            using (SqlDataReader dr = sqlCmd.ExecuteReader())
            {
                List<Baiviet> glstBaiviet = new List<Baiviet>();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Baiviet bv = new Baiviet();
                        bv.ID = Convert.ToInt64(dr["ID"]);
                        bv.Mabaiviet = Convert.ToString(dr["Mabaiviet"]);
                        bv.Tenbaiviet = Convert.ToString(dr["Tenbaiviet"]);
                        bv.Shortcut = Convert.ToString(dr["shortcut"]);
                        bv.Noidung = Convert.ToString(dr["noidung"]);
                        bv.Anhdaidien = Convert.ToString(dr["anhdaidien"]);

                        glstBaiviet.Add(bv);
                    }
                    dr.Close();
                    sqlCmd.Connection.Close();
                }
                return glstBaiviet;
            }
        }
    }
}
